package com.sfac.javaEE.dao;

import com.sfac.javaEE.entity.User;
import com.sfac.javaEE.util.DBUtil;

import java.sql.*;

public class UserDao {
    public User insertUser(User user) {
        Connection co = null;
        try {
            co = DBUtil.getConnection();
            // 准备 sql preparedStatement
            String sql = "insert into account_user(user_name, password, create_date) " +
                    "values(?, ?, ?)";
            PreparedStatement ps = co.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getPassword());
            ps.setTimestamp(3, new Timestamp(user.getCreateDate().getTime()));
            System.out.println(ps.toString());
            // 执行 sql 语句
            ps.execute();
            // 处理结果
            ResultSet rs = ps.getGeneratedKeys();
            while(rs.next()) {
                user.setId(rs.getInt(1));
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DBUtil.closeConnection(co);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }

    public User getUserByUserName(String userName) {
        Connection co = null;
        User user = null;
        try {
            co = DBUtil.getConnection();
            // 准备 sql preparedStatement
            String sql = "select * from account_user where user_name = ?";
            PreparedStatement ps = co.prepareStatement(sql);
            ps.setString(1, userName);
            System.out.println(ps.toString());
            // 执行 sql 语句
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUserName(rs.getString("user_name"));
                user.setPassword(rs.getString("password"));
                user.setCreateDate(rs.getTimestamp("create_date"));
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DBUtil.closeConnection(co);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return user;
    }

    public void updateUser(User user) {
        Connection co = null;
        try {
            co = DBUtil.getConnection();
            // 准备 sql preparedStatement
            String sql = "update account_user set user_name=?, password=? where id=?";
            PreparedStatement ps = co.prepareStatement(sql);
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getPassword());
            ps.setInt(3, user.getId());
            System.out.println(ps.toString());
            // 执行 sql 语句
            ps.execute();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DBUtil.closeConnection(co);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public User getUserById(int id) {
        Connection co = null;
        User user = null;
        try {
            co = DBUtil.getConnection();
            // 准备 sql preparedStatement
            String sql = "select * from account_user where id = ?";
            PreparedStatement ps = co.prepareStatement(sql);
            ps.setInt(1, id);
            System.out.println(ps.toString());
            // 执行 sql 语句
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUserName(rs.getString("user_name"));
                user.setPassword(rs.getString("password"));
                user.setCreateDate(rs.getTimestamp("create_date"));
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DBUtil.closeConnection(co);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return user;
    }

    public void deleteUserById(int id) {
        Connection co = null;
        try {
            co = DBUtil.getConnection();
            // 准备 sql preparedStatement
            String sql = "delete from account_user where id = ?";
            PreparedStatement ps = co.prepareStatement(sql);
            ps.setInt(1, id);
            System.out.println(ps.toString());
            // 执行 sql 语句
            ps.execute();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DBUtil.closeConnection(co);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public User getUserByUserNameAndPassword(String userName, String password) {
        Connection co = null;
        User user = null;
        try {
            co = DBUtil.getConnection();
            // 准备 sql preparedStatement
            String sql = "select * from account_user where user_name = ? and password = ? limit 1";
            PreparedStatement ps = co.prepareStatement(sql);
            ps.setString(1, userName);
            ps.setString(2, password);
            System.out.println(ps.toString());
            // 执行 sql 语句
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUserName(rs.getString("user_name"));
                user.setPassword(rs.getString("password"));
                user.setCreateDate(rs.getTimestamp("create_date"));
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DBUtil.closeConnection(co);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return user;
    }
}
